import sqlite3

# 1. 连接数据库（如果不存在会自动创建）
conn = sqlite3.connect('auto_cdn.db')  # 数据库文件名为 example.db
cursor = conn.cursor()

# 2. 执行 SQL 语句
# 创建表
# cursor.execute("""
#     CREATE TABLE IF NOT EXISTS users (
#         id INTEGER PRIMARY KEY AUTOINCREMENT,
#         name TEXT NOT NULL,
#         age INTEGER
#     )
# """)

# 插入数据
cursor.execute("""
    INSERT INTO users (username, password, nickname, email, role, others) 
    VALUES (?, ?, ?, ?, ?, ?)
""", ("user", "$2b$12$0aV9QGEX3tpP4dlITjZ9z.teTdfJyM3jFaAqNbEfZddRpTn8/jWSK", "Regular User", "alice@example.com", 0, "{}"))

cursor.execute("""
    INSERT INTO users (username, password, nickname, email, role, others) 
    VALUES (?, ?, ?, ?, ?, ?)
""", ("admin", "$2b$12$3KwQrnPaL7/e3PEA6b0jHOLub7dHMWuhsSBrCxj9O6KX/rbgvMU0O", "Administrator", "admin@example.com", 1, "{}"))


# 查询数据
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()  # 获取所有结果
for row in rows:
    print(row)  # 输出：(1, 'Alice', 25)

# 3. 提交事务并关闭连接
conn.commit()  # 重要！否则修改不会保存
conn.close()